﻿/****************
 * Copyright 2016 Brian Tabone
 * Licensed under the Apache 2.0 License
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * IAODal.cs
 * Data access layer for IAO systems
 * 9/11/2016
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using IAO.Common.ServiceFramework.RuntimeProperties;
using log4net;

namespace IAO.Common.ServiceFramework.DataAccessLayer
{
    public abstract class IAODal : IDisposable
    {
        /// <summary>
        ///     Hide the default constructor, we must have a logger to operate
        /// </summary>
        private IAODal()
        {
        }

        /// <summary>
        ///     Construct a data access layer object and assign its logger so we log to the applications log files
        /// </summary>
        /// <param name="log"></param>
        /// <param name="databaseConnectionString">The connection string for the database</param>
        /// <param name="schemaVersion">The version of the schema (will be created if it doesn't exist</param>
        protected IAODal(ILog log, string databaseConnectionString, int schemaVersion)
        {
            Logger = log;
            if (!Connect(databaseConnectionString))
            {
                throw new DataException("Unable to open connection to the database");
            }

            InitializeSchema(schemaVersion);
            Logger.Info($"Database connected. SchemaVersion is {schemaVersion}");
        }

        public DbConnection SqlLiteConnection { get; private set; }
        protected ILog Logger { get; }

        /// <summary>
        ///     Close the database connection
        /// </summary>
        public void Dispose()
        {
            if (SqlLiteConnection != null)
            {
                if (SqlLiteConnection.State != ConnectionState.Closed)
                {
                    try
                    {
                        SqlLiteConnection.Close();
                        SqlLiteConnection = null;
                    }
                    catch (Exception ex)
                    {
                        Logger.Warn($"Unable to close connection: {ex.Message}");
                    }
                }
            }
        }

        /// <summary>
        ///     Attempt to connect to the specified database
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public bool Connect(string dbName)
        {
            try
            {
                var factory = DbProviderFactories.GetFactory("System.Data.SQLite");
                SqlLiteConnection = factory.CreateConnection();
                SqlLiteConnection.ConnectionString = $"Data Source={dbName}";
                SqlLiteConnection.Open();

                Logger.Info($"Database connection {dbName} opened");
                return ConnectionState.Open == (SqlLiteConnection.State & ConnectionState.Open);
            }
            catch (Exception ex)
            {
                Logger.Warn($"Unable to connect to database {dbName}: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        ///     Setup the schema if it is not already initalized, do nothing if it already is. Will throw an exception if the
        ///     existing schema mismatches the requested schema
        /// </summary>
        protected void InitializeSchema(int schemaVersion)
        {
            try
            {
                var version = GetSchemaVersion();
                if (version != schemaVersion)
                {
                    throw new DataException($"Found schema version {version}, expected {schemaVersion}");
                }
            }
            catch (SQLiteException sqlEx)
            {
                Logger.Info($"No version table found {sqlEx.Message}");
                SetSchemaVersion(schemaVersion);
            }
        }

        /// <summary>
        ///     Get the schema version on this connection. Throws exception if table not found, callers should trap
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <returns>version of schema</returns>
        private int GetSchemaVersion()
        {
            using (IDbCommand checkSchemaVersion = SqlLiteConnection.CreateCommand())
            {
                checkSchemaVersion.CommandType = CommandType.Text;
                checkSchemaVersion.CommandText = "SELECT [Version] FROM SchemaVersion";

                var dataReader = checkSchemaVersion.ExecuteReader();

                dataReader.Read(); // Should only be one row in this table
                var version = (int) dataReader["Version"];
                dataReader.Close();

                return version;
            }
        }

        /// <summary>
        ///     Create the schema table and set the schema version. Use UpdateSchemaVersion to update existing schema
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="version">The schema version to set</param>
        private void SetSchemaVersion(int version)
        {
            using (IDbCommand createSchemaCommand = SqlLiteConnection.CreateCommand())
            {
                createSchemaCommand.CommandType = CommandType.Text;
                createSchemaCommand.CommandText =
                    $@"CREATE TABLE SchemaVersion
                    (
                        Version INT,
                        CreateDT TEXT
                    )
                    ";
                createSchemaCommand.ExecuteNonQuery();

                createSchemaCommand.CommandText =
                    $@"INSERT INTO SchemaVersion (Version, CreateDT) VALUES ('{version}', '{DateTime
                        .UtcNow}');";
                createSchemaCommand.ExecuteNonQuery();
            }

            CreateSchema();

            Logger.Info($"Created schema version {version}");
        }


        /// <summary>
        ///     Subclasses should override this and have it do the work of setting up the schema, it will get called whenever the
        ///     schema table has to be created
        /// </summary>
        protected virtual void CreateSchema()
        {
            // All overriders should call base.CreateSchema(), here we create the shared schema elements such as the property tables
            using (IDbCommand createSchemaCommand = SqlLiteConnection.CreateCommand())
            {
                createSchemaCommand.CommandType = CommandType.Text;
                createSchemaCommand.CommandText =
                    $@"CREATE TABLE PersistentProperties
                    (
                        Id INT PRIMARY KEY,
                        ParentID INT NULL,
                        Class TEXT NOT NULL,
                        Name TEXT,
                        ReadOnly BOOL,
                        CreateDT DATETIME,
                        Type TEXT,
                        Value TEXT                                      
                    )
                    ";
                createSchemaCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Add a property to the PersistentProperties table
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="type"></param>
        /// <param name="readOnly"></param>
        public void AddProperty(int id, string classStr, string name, string value, string type, bool readOnly)
        {
            using (IDbCommand addPropertyCommand = SqlLiteConnection.CreateCommand())
            {
                addPropertyCommand.CommandType = CommandType.Text;
                addPropertyCommand.CommandText =
                    $@"INSERT INTO PersistentProperties ('Id', 'Class', 'Name', 'ReadOnly', 'CreateDT', 'Type', 'Value') 
                    VALUES (
                        @id,
                        @class,
                        @name,
                        @readonly,
                        @createDT,
                        @type,
                        @value                                     
                    )
                    ";
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@id", id));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@class", classStr));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@name", name));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@readonly", readOnly));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@createDT", DateTime.UtcNow));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@type", type));
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@Value", value));

                addPropertyCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Update a property (caller specifies all values, even those that don't change. readOnly flag is not changeable)
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="type"></param>
        public void UpdateProperty(int id, string value)
        {
            if (PropertyIsReadOnly(id))
            {
                throw new ArgumentException("Property is readonly, cannot be updated");
            }

            using (IDbCommand addPropertyCommand = SqlLiteConnection.CreateCommand())
            {
                addPropertyCommand.CommandType = CommandType.Text;
                addPropertyCommand.CommandText =
                    $@"UPDATE PersistentProperties SET
                        Value=@value
                    WHERE Id='{id}'                                     
                    ";
                addPropertyCommand.Parameters.Add(new SQLiteParameter("@Value", value));

                addPropertyCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Returns true if property id exists in the persistent store
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool PropertyExists(int id)
        {
            using (IDbCommand testPropertyExistsCmd = SqlLiteConnection.CreateCommand())
            {
                testPropertyExistsCmd.CommandType = CommandType.Text;
                testPropertyExistsCmd.CommandText =
                    $@"SELECT Id FROM PersistentProperties WHERE Id = {id}";

                var dbResult = testPropertyExistsCmd.ExecuteScalar();

                return dbResult != null;
            }
        }


        /// <summary>
        ///     Return true if a property is marked readonly
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool PropertyIsReadOnly(int id)
        {
            using (IDbCommand testPropertyExistsCmd = SqlLiteConnection.CreateCommand())
            {
                testPropertyExistsCmd.CommandType = CommandType.Text;
                testPropertyExistsCmd.CommandText =
                    $@"SELECT ReadOnly FROM PersistentProperties WHERE Id = {id}";

                try
                {
                    using (var reader = testPropertyExistsCmd.ExecuteReader())
                    {
                        reader.Read();
                        return (bool) reader["ReadOnly"];
                    }
                }
                catch (Exception ex)
                {
                    Logger.Debug("Error tetsing readonly on property", ex);
                    throw;
                }
            }
        }

        /// <summary>
        ///     Read all properties from the database (non-volatile, all others are created at run time)
        /// </summary>
        /// <returns></returns>
        public List<Property> GetAllNonVolatileProperties()
        {
            var properties = new List<Property>();
            using (IDbCommand testPropertyExistsCmd = SqlLiteConnection.CreateCommand())
            {
                testPropertyExistsCmd.CommandType = CommandType.Text;
                testPropertyExistsCmd.CommandText =
                    $@"SELECT * FROM PersistentProperties";

                using (var reader = testPropertyExistsCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        properties.Add( new Property((int)reader["Id"], (string)reader["Class"], (string)reader["Name"], (string)reader["Value"], (string)reader["Type"], (bool)reader["ReadOnly"]));
                    }
                }
            }

            return properties;
        }
    }
}